package DB
{
	public class LocalPersistence
	{
		/// DATABASE PART.......................................
		import flash.data.SQLConnection;
		import flash.data.SQLResult;
		import flash.data.SQLStatement;
		import flash.errors.SQLError;
		import flash.events.Event;
		import flash.events.KeyboardEvent;
		import flash.events.SQLErrorEvent;
		import flash.events.SQLEvent;
		import flash.filesystem.File;
		
		import forms.Utils;
		
		import mx.collections.ArrayList;
		import mx.events.CollectionEvent;
		
		import spark.components.View;
		
		//var dbFile:File = File.desktopDirectory.resolvePath("GibDB.db"); //ALWAYS COMMENT  BEFORE RELEASE!
		var dbFile:File = File.applicationStorageDirectory.resolvePath("GibDB.db");//SOS! Uncomment this before release!
		
		public function LocalPersistence()
		{
			
		}
		
		var dbTemplate:File = new File();
		private  var initConn = false;
		private  var conn:SQLConnection = new SQLConnection();
		private  var createStmt:SQLStatement = new SQLStatement();
		private  var selectStmt:SQLStatement = new SQLStatement();
		private  var insertStmt:SQLStatement = new SQLStatement();
		private  var deleteStmt:SQLStatement = new SQLStatement();
		private  var updateStmt:SQLStatement = new SQLStatement();
		
		public var result:SQLResult = new SQLResult();
		
	
		
		public function viewActivated_handler(evt:Event)
		{ 
			if(initConn)
			{
				
			}
		}
		
		public  function realInit()
		{
				if(conn.connected==true)
				{}
				else
				{
					try
					{
						conn.open(dbFile);
						tables();
					}
					catch (error:SQLError)
					{
						trace("Error message:", error.message);
						trace("Details:", error.details);
					}
				}
			
		}
		
		public  function Init()
		{
			try
			{
				conn.open(dbFile);
				//tables();
				
			}
			catch (error:SQLError)
			{
				trace("Error message:", error.message);
				trace("Details:", error.details);
			}
		}
		
		
		public  function tables():void
		{
			
			createStmt.sqlConnection = conn;
			var sql:String = 
				"CREATE TABLE IF NOT EXISTS GibView1 (" + 
				"    uid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " + 
				"    date TEXT, " 		+ 
				"    month TEXT, " 		+ 
				"    year TEXT," 		+
				"    name TEXT," 		+
				"	 phone TEXT,"	 	+
				"	 mphone TEXT," 		+
				"	 mail TEXT,"		+
				"    fav  BOOL,"		+
				"    tweetName TEXT,"	+
				"	 tweetURL  TEXT" 	+
				")";
			//alter table name addColumn "complete TEXT"
			
			createStmt.text = sql;
			createStmt.execute();
			createStmt.addEventListener(SQLEvent.RESULT, createResult);
			createStmt.addEventListener(SQLErrorEvent.ERROR, createError);
		}
		
		
		public  function createError(event:SQLErrorEvent):void
		{
			trace("SQL tables error: "+event.error);
			//Alert.show("An error occured....");
		}
		
		public  function createResult(event:SQLEvent):void
		{}
		
		var datesArr = new Array();
		public var wins = 0;
		public var total = 0;
		public var loss = 0;
		public var LastGameLost = false;
		public var dataDG;
		
		public  function nowShow(filter=false)
		{
			selectStmt.sqlConnection = conn;
			if(filter!=false)
			{
				selectStmt.text = "SELECT * FROM GibView1 WHERE name LIKE '"+filter+"%' ORDER BY fav DESC";
				//selectStmt.parameters[":filter"] = String(filter)+"%";
				//selectStmt.parameters[":filterCaps"] = String(filter).toUpperCase()+"%";
			}
			else
				selectStmt.text = "SELECT * FROM GibView1 ORDER BY fav DESC";
			
			// This try..catch block is fleshed out in
			// a subsequent code listing
			try
			{
				selectStmt.execute();
				result = selectStmt.getResult();
				
				return result;				
				var index = 0;
				
			}
			catch (error:SQLError)
			{
				trace("Error in now show: "+error.details);//Alert.show("no data for this month!");
			}
			
			
		}
		
		function onUID(obj1, obj2)
		{
			if(obj2.fav==true)
				return 1;
			else if(obj1.fav==true)
				return -1;
			else
				return 0;
		}
		
		//adding gib ...
		public  function addGibInfo(name,phone,mphone,mail,favStatus,tweetName,tweetURL)
		{
			//deleteNow();
			insertStmt.sqlConnection = conn;
			var today_date:Date = new Date();
			var sql:String = 
				"INSERT INTO GibView1 (date, month, year,name,phone,mphone,mail,fav,tweetName, tweetURL) " + 
				"VALUES (:date, :month, :year,:name,:phone, :mphone, :mail,:fav,:tweetName, :tweetURL)";
			insertStmt.text = sql;
			//insertStmt.parameters[":ID"] = add()
			var y = new Date().fullYear;
			var date:Date = new Date();
			insertStmt.parameters[":month"] 	= date.getMonth();
			insertStmt.parameters[":date"] 		= date.date;
			insertStmt.parameters[":year"] 		= y;
			insertStmt.parameters[":name"] 		= name;
			insertStmt.parameters[":phone"] 	= phone;
			insertStmt.parameters[":mphone"] 	= mphone;
			insertStmt.parameters[":mail"] 		= mail;
			insertStmt.parameters[":fav"] 		= favStatus;
			insertStmt.parameters[":tweetName"] = tweetName;
			insertStmt.parameters[":tweetURL"]  = tweetURL;
			try
			{
				// execute the statement
				insertStmt.execute();
				
				//Alert.show("All done!");
				//nowShow();
				
			}
			catch (error:SQLError)
			{
				trace("Error occured on insert: "+error.details);//Alert.show((error.details).toString());
			}
		}
		
		
		
		
		
		// done in the nowShow function
		public function filterGibs(params)
		{
			
		}
		
		public function editGib(data,newData)
		{
			updateGib(newData);
			//deleteNow(data.uid,true);
			//addGibInfo(newData.name,newData.phone,newData.mphone,newData.mail,newData.fav,newData.tweetName, newData.tweetURL);
		}
		
		////////////////// UPDATE ///////////////////////////////////
		public function updateGib(data, silent=true)
		{
			updateStmt.sqlConnection = conn;
			
			updateStmt.text = "UPDATE GibView1 SET "+
				"name = :name,date = :date,month = :month,year = :year,phone = :phone,mphone = :mphone,mail = :mail,fav = :fav,tweetName = :tweetName,tweetURL = :tweetURL "+
				"WHERE uid = :filter";
			
			updateStmt.parameters[":filter"] = data.uid;
			var y = new Date().fullYear;
			var date:Date = new Date();
			updateStmt.parameters[":month"] 	= date.getMonth();
			updateStmt.parameters[":date"] 		= date.date;
			updateStmt.parameters[":year"] 		= y;
			updateStmt.parameters[":name"] 		= data.name;
			updateStmt.parameters[":phone"] 	= data.phone;
			updateStmt.parameters[":mphone"] 	= data.mphone;
			updateStmt.parameters[":mail"] 		= data.mail;
			updateStmt.parameters[":fav"] 		= data.favStatus;
			updateStmt.parameters[":tweetName"] = data.tweetName;
			updateStmt.parameters[":tweetURL"]  = data.tweetURL;
			
			try
			{
				updateStmt.execute();
			}
			catch (error:SQLError)
			{
				trace("Error on Update: "+error.details);
			}
		}
		
		
		////////////// DELETE/CHANGE FUNCTIONS ///////////////////////
		//////////////////////////////////////////////////////////////
		
		public  function deleteNow(uid,silent=false):void
		{
			deleteStmt.sqlConnection = conn;
			
			deleteStmt.text = "DELETE FROM GibView1 WHERE uid = :param1";
			deleteStmt.parameters[":param1"] = uid;
			
			// This try..catch block is fleshed out in
			// a subsequent code listing
			try
			{
				deleteStmt.execute();
			}
			catch (error:SQLError)
			{
				trace("Error on delete: "+error.details);//Alert.show("no data!");
			}
			if(!silent)
				nowShow();
		}
		
		public  function changeFavStatus(uid,status,info:Object)
		{
			var data = {
				"name"		:info.name,
				'phone'		:info.phone,
				'mphone'	:info.mphone,
				'mail'		:info.mail,
				'favStatus'	:status,
				'tweetName'	:info.tweetName, 
				'tweetURL'	:info.tweetURL,
				'uid'		:uid
			}
			updateGib(data);
			//deleteNow(uid,true);
			//addGibInfo(info.name,info.phone,info.mphone,info.mail,status,info.tweetName, info.tweetURL);
		}
		
	}
}